﻿
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OpenLab.Model;

namespace OpenLab.DAL
{
	public partial class DemandeDAL : OpenLab.DAL.IDemandeDAL
	{
        public Demande Add
			(Demande demande)
		{
				string sql ="INSERT INTO Demande (datedemande, idclient, dateprelevement, dateordenance, idmedecin, montanttotal, montantclient, montantorganisme, infomedicale, iduser)  output inserted.id VALUES (@datedemande, @idclient, @dateprelevement, @dateordenance, @idmedecin, @montanttotal, @montantclient, @montantorganisme, @infomedicale, @iduser)";
				SqlParameter[] para = new SqlParameter[]
					{
						new SqlParameter("@datedemande", ToDBValue(demande.datedemande)),
						new SqlParameter("@idclient", ToDBValue(demande.idclient)),
						new SqlParameter("@dateprelevement", ToDBValue(demande.dateprelevement)),
						new SqlParameter("@dateordenance", ToDBValue(demande.dateordenance)),
						new SqlParameter("@idmedecin", ToDBValue(demande.idmedecin)),
						new SqlParameter("@montanttotal", ToDBValue(demande.montanttotal)),
						new SqlParameter("@montantclient", ToDBValue(demande.montantclient)),
						new SqlParameter("@montantorganisme", ToDBValue(demande.montantorganisme)),
						new SqlParameter("@infomedicale", ToDBValue(demande.infomedicale)),
						new SqlParameter("@iduser", ToDBValue(demande.iduser)),
					};
					
				int newId = (int)SqlHelper.ExecuteScalar(sql, para);
				return GetById(newId);
		}

        public int DeleteById(int id)
		{
            string sql = "DELETE Demande WHERE Id = @Id";

           SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@id", id)
			};
		
            return SqlHelper.ExecuteNonQuery(sql, para);
		}
		
				
        public int Update(Demande demande)
        {
            string sql =
                "UPDATE Demande " +
                "SET " +
			" datedemande = @datedemande" 
                +", idclient = @idclient" 
                +", dateprelevement = @dateprelevement" 
                +", dateordenance = @dateordenance" 
                +", idmedecin = @idmedecin" 
                +", montanttotal = @montanttotal" 
                +", montantclient = @montantclient" 
                +", montantorganisme = @montantorganisme" 
                +", infomedicale = @infomedicale" 
                +", iduser = @iduser" 
               
            +" WHERE id = @id";


			SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@id", demande.id)
					,new SqlParameter("@datedemande", ToDBValue(demande.datedemande))
					,new SqlParameter("@idclient", ToDBValue(demande.idclient))
					,new SqlParameter("@dateprelevement", ToDBValue(demande.dateprelevement))
					,new SqlParameter("@dateordenance", ToDBValue(demande.dateordenance))
					,new SqlParameter("@idmedecin", ToDBValue(demande.idmedecin))
					,new SqlParameter("@montanttotal", ToDBValue(demande.montanttotal))
					,new SqlParameter("@montantclient", ToDBValue(demande.montantclient))
					,new SqlParameter("@montantorganisme", ToDBValue(demande.montantorganisme))
					,new SqlParameter("@infomedicale", ToDBValue(demande.infomedicale))
					,new SqlParameter("@iduser", ToDBValue(demande.iduser))
			};

			return SqlHelper.ExecuteNonQuery(sql, para);
        }		
		
        public Demande GetById(int id)
        {
            string sql = "SELECT * FROM Demande WHERE Id = @Id";
            using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, new SqlParameter("@Id", id)))
			{
				if (reader.Read())
				{
					return ToModel(reader);
				}
				else
				{
					return null;
				}
       		}
        }
		
		public Demande ToModel(SqlDataReader reader)
		{
			Demande demande = new Demande();

			demande.id = (int)ToModelValue(reader,"id");
			demande.datedemande = (DateTime)ToModelValue(reader,"datedemande");
			demande.idclient = (int)ToModelValue(reader,"idclient");
			demande.dateprelevement = (DateTime)ToModelValue(reader,"dateprelevement");
			demande.dateordenance = (DateTime?)ToModelValue(reader,"dateordenance");
			demande.idmedecin = (int?)ToModelValue(reader,"idmedecin");
			demande.montanttotal = (decimal)ToModelValue(reader,"montanttotal");
			demande.montantclient = (decimal)ToModelValue(reader,"montantclient");
			demande.montantorganisme = (decimal)ToModelValue(reader,"montantorganisme");
			demande.infomedicale = (string)ToModelValue(reader,"infomedicale");
			demande.iduser = (int)ToModelValue(reader,"iduser");
			return demande;
		}
		
		public int GetTotalCount()
		{
			string sql = "SELECT count(*) FROM Demande";
			return (int)SqlHelper.ExecuteScalar(sql);
		}
		
		public IEnumerable<Demande> GetPagedData(int minrownum,int maxrownum)
		{
			string sql = "SELECT * from(SELECT *,row_number() over(order by id) rownum FROM Demande) t where rownum>=@minrownum and rownum<=@maxrownum";
			using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql,
				new SqlParameter("@minrownum",minrownum),
				new SqlParameter("@maxrownum",maxrownum)))
			{
				return ToModels(reader);					
			}
		}
		
		public IEnumerable<Demande> GetAll()
		{
			string sql = "SELECT * FROM Demande";
			using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql))
			{
				return ToModels(reader);			
			}
		}
		
		protected IEnumerable<Demande> ToModels(SqlDataReader reader)
		{
			var list = new List<Demande>();
			while(reader.Read())
			{
				list.Add(ToModel(reader));
			}	
			return list;
		}		
		
		protected object ToDBValue(object value)
		{
			if(value==null)
			{
				return DBNull.Value;
			}
			else
			{
				return value;
			}
		}
		
		protected object ToModelValue(SqlDataReader reader,string columnName)
		{
			if(reader.IsDBNull(reader.GetOrdinal(columnName)))
			{
				return null;
			}
			else
			{
				return reader[columnName];
			}
		}
	}
}
